package DB;

import java.sql.*;
import java.util.ArrayList;

import Model.AttendanceView;

public class AttendanceViewDB implements AttendanceViewDBIF{

    private Connection conn;

    public AttendanceViewDB()
    {
        conn = ConnectionDB.getInstance().getConn();
    }

    @Override
    public ArrayList<AttendanceView> getAllAttendanceViews(boolean retrieveAssociation) {
       return miscWhere("",retrieveAssociation);
    }

    @Override
    public ArrayList<AttendanceView> getAllAttendanceViewsBetweenDates(boolean retrieveAssociation,String startDate,String endDate) {
        return miscWhere("date BETWEEN '"+startDate+"' AND '"+endDate+"'",retrieveAssociation);
    }

    @Override
    public ArrayList<AttendanceView> getAllAttendanceViewForOneStudentById(boolean retrieveAssociation,int studentId){
        return miscWhere("studentId = "+studentId ,retrieveAssociation);
    }

    @Override
    public ArrayList<AttendanceView> getAllAttendanceViewForOneStudentByIdAndBetweenDates(boolean retrieveAssociation,int studentId, String startDate,String endDate){
        return miscWhere("studentId = "+studentId+ "AND date BETWEEN '" +startDate+ "' AND '" +endDate+ "'",retrieveAssociation);
    }

    @Override
    public ArrayList<AttendanceView> getAllAttendanceViewByCourse(boolean retrieveAssociation,int courseId){
        return miscWhere("courseId = "+courseId,retrieveAssociation);
    }

    @Override
    public ArrayList<Integer> getAllDistinctCoursesByStudentId(boolean retrieveAssociation,int studentId){
        return miscDistinctWhere("studentId = " + studentId, retrieveAssociation);
    }


    @Override
    public ArrayList<AttendanceView> getAllAttendanceViewByStudentAndCourse(boolean retrieveAssociation,int studentId,int courseId){
        return miscWhere("studentId = " + studentId + "AND courseId = " + courseId, retrieveAssociation);
    }

    @Override
    public ArrayList<AttendanceView> getAllAttendanceViewByStudentAndCourseBetweenDates(boolean retrieveAssociation,int studentId,int courseId,String startDate,String endDate){
        return miscWhere("studentId = "+studentId+" AND courseId = "+courseId+" AND date BETWEEN '" +startDate +"' AND '"+endDate+"'",retrieveAssociation);
    }

    //Finding all AttendanceView
    private ArrayList<AttendanceView> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<AttendanceView> list = new ArrayList<AttendanceView>();
        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                AttendanceView attendanceObj = buildAttendanceView(results);
                list.add(attendanceObj);
            }

            stmt.close();

        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }


    //Method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT * FROM attendanceView";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }


    //Finding all AttendanceView
    private ArrayList<Integer> miscDistinctWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Integer> list = new ArrayList<Integer>();
        String query = buildDistinctQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {

                list.add(results.getInt("courseId"));
            }

            stmt.close();

        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }


    //Method to build the query
    private String buildDistinctQuery(String wClause) {
        String query = "SELECT DISTINCT courseId FROM attendanceView";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query; }
    
    
    //Method to build a Attendance object
    private AttendanceView buildAttendanceView(ResultSet results) {

        AttendanceView attendanceObj = new AttendanceView();

        try {
            attendanceObj.setAttandanceId(results.getInt("attendanceId"));
            attendanceObj.setComment(results.getString("comment"));
            attendanceObj.setAttending(results.getBoolean("isAttending"));
            attendanceObj.setStudentId(results.getInt("studentId"));
            attendanceObj.setSessionId(results.getInt("sessionId"));
            attendanceObj.setDate(results.getString("date"));
            attendanceObj.setCourseId(results.getInt("courseId"));
            attendanceObj.setName(results.getString("name"));
        }
        catch (Exception e) {
            System.out.println("Error building Attendance object");
        }

        return attendanceObj;
    }
}
